Declare @StartDate datetime
Declare @EndDate datetime

SET @StartDate = '01-Jan-2014'
SET @EndDate = '31-Jan-2014'

--SELECT distinct E.[EmailID]
--	  ,E.EmailCaseRefNo As [Case ID]
--	  ,E.EmailSubject
--	  ,CT.[Description] AS CaseType
--	  ,E.CreatedDate As ReceivedOn
--      ,CPI.[ProcessedBy]
--	  ,CPI.[ProcessedOn]
--	  ,CPI.[ClosedBy]
--	  ,TM.TeamName As ProcessByDivision
--	  ,U.DisplayName As ProcessByOfficer
--	  ,TM2.TeamName As ClosedByDivision
--	  ,U2.DisplayName As ClosedByOfficer
--	  ,(select MIN(TH.CreatedDate) from tblTaskHistory TH 
--			INNER JOIN tblTeam TE ON TH.EscalatedTeamID = TE.TeamID
--			Where TE.TeamTypeID = 2 And TH.TaskID = T.TaskID) As EsclatedToACRAOn
--	  ,(dbo.funcGetWorkingDays(T.CaseReceivedDate, 
--		(select MIN(TH.CreatedDate) from tblTaskHistory TH 
--			INNER JOIN tblTeam TE ON TH.EscalatedTeamID = TE.TeamID
--			Where TE.TeamTypeID = 2 And TH.TaskID = T.TaskID)
--		)) EsclatedToACRAAfterNoOfWorkingDays
--	  ,CPI.ClosedOn
--	  ,CASE 
--		WHEN (CPI.NoOfWorkingDays > 7) THEN '> 7D'
--		WHEN (CPI.NoOfWorkingDays <= 3) THEN '>= 1D and =< 3D'
--		WHEN (CPI.NoOfWorkingDays Between 4 And 7) THEN '> 3D And =< 7D'
--		END ServiceStandard
--	  ,CPI.NoOfWorkingDays
--	  ,CPI.NoOfPHAndWeekendDays
--	  ,CPI.CaseStatus
--	  ,S.StatusDesc
--	  ,T.[Status] AS TaskStatus
--	  ,S2.StatusDesc As TaskStatusDesc
--  FROM  tblCaseProfileIndex CPI
--		INNER JOIN tblEntity EN ON CPI.EntityID = EN.EntityID
--		INNER JOIN tblContactProfile CP ON CPI.ContactProfileID = CP.ContactProfileID
--		INNER JOIN [tblEmail] E  ON E.EmailID = CPI.CaseRefID
--		INNER JOIN tblStatusInfo S ON CPI.CaseStatus = S.StatusID
--		INNER JOIN tblCaseTypeInfo CT ON CPI.CaseTypeID = CT.CaseTypeID
--		INNER JOIN tblUser U ON CPI.[ProcessedBy] = U.UserID
--		INNER JOIN tblTeam TM ON U.TeamID = TM.TeamID
--		INNER JOIN tblUser U2 ON CPI.[ClosedBy] = U2.UserID
--		INNER JOIN tblTeam TM2 ON U2.TeamID = TM2.TeamID
--		LEFT OUTER JOIN tblTask T ON CPI.CaseRefID = T.CaseRefID And T.CaseSourceID = 2
--		LEFT OUTER JOIN tblStatusInfo S2 ON T.[Status] = S2.StatusID
--	WHERE CPI.CaseSourceID = 2 And CPI.IsReplyCase = 0
--	And CPI.CreatedDate Between @StartDate And @EndDate
--	Order By E.CreatedDate ASC

--select * from tblCaseProfileIndex
--Where CaseSourceID = 1 And IsReplyCase = 0
--And CreatedDate Between @StartDate And @EndDate
--And CaseStatus <> 100

--SELECT Count(*)
--  FROM  tblCaseProfileIndex CPI
--		INNER JOIN tblEntity EN ON CPI.EntityID = EN.EntityID
--		INNER JOIN tblContactProfile CP ON CPI.ContactProfileID = CP.ContactProfileID
--		INNER JOIN [tblAskACRA] A  ON A.AskACRAID = CPI.CaseRefID
--		INNER JOIN tblCaseSubjectInfo CS ON CS.CaseSubjectID = A.CaseSubjectID
--		INNER JOIN tblStatusInfo S ON CPI.CaseStatus = S.StatusID
--		INNER JOIN tblCaseTypeInfo CT ON CPI.CaseTypeID = CT.CaseTypeID
--		INNER JOIN tblUser U ON CPI.[ProcessedBy] = U.UserID
--		INNER JOIN tblTeam TM ON U.TeamID = TM.TeamID
--		--INNER JOIN tblUser U2 ON CPI.[ClosedBy] = U2.UserID
--		--INNER JOIN tblTeam TM2 ON U2.TeamID = TM2.TeamID
--	WHERE CPI.CaseSourceID = 1 And CPI.IsReplyCase = 0
--	And CPI.CreatedDate Between @StartDate And @EndDate
	

--SELECT distinct A.AskACRAID
--	  ,A.CaseRefNo As [Case ID]
--	  ,CS.[Subject] As CaseSubject
--	  ,CT.[Description] AS CaseType
--	  ,A.CreatedDate As ReceivedOn
--      ,CPI.[ProcessedBy]
--	  ,CPI.[ProcessedOn]
--	  ,CPI.[ClosedBy]
--	  ,TM.TeamName As ProcessByDivision
--	  ,U.DisplayName As ProcessByOfficer
--	  ,TM2.TeamName As ClosedByDivision
--	  ,U2.DisplayName As ClosedByOfficer
--	  ,(select MIN(TH.CreatedDate) from tblTaskHistory TH 
--			INNER JOIN tblTask T ON TH.TaskID = T.TaskID
--			INNER JOIN tblTeam TE ON TH.EscalatedTeamID = TE.TeamID
--			Where TE.TeamTypeID = 2 And T.CaseProfileIndex = CPI.CaseProfileIndex) As EsclatedToACRAOn
--	  ,(dbo.funcGetWorkingDays(CPI.CreatedDate, 
--		(select MIN(TH.CreatedDate) from tblTaskHistory TH 
--			INNER JOIN tblTask T ON TH.TaskID = T.TaskID
--			INNER JOIN tblTeam TE ON TH.EscalatedTeamID = TE.TeamID
--			Where TE.TeamTypeID = 2 And T.CaseProfileIndex = CPI.CaseProfileIndex)
--		)) EsclatedToACRAAfterNoOfWorkingDays
--	  ,CPI.ClosedOn
--	  ,CASE 
--		WHEN (CPI.NoOfWorkingDays > 7) THEN '> 7D'
--		WHEN (CPI.NoOfWorkingDays <= 3) THEN '>= 1D and =< 3D'
--		WHEN (CPI.NoOfWorkingDays Between 4 And 7) THEN '> 3D And =< 7D'
--		END ServiceStandard
--	  ,CPI.NoOfWorkingDays
--	  ,CPI.NoOfPHAndWeekendDays
--	  ,CPI.CaseStatus
--	  ,S.StatusDesc
--  FROM  tblCaseProfileIndex CPI
--		INNER JOIN tblEntity EN ON CPI.EntityID = EN.EntityID
--		INNER JOIN tblContactProfile CP ON CPI.ContactProfileID = CP.ContactProfileID
--		INNER JOIN [tblAskACRA] A  ON A.AskACRAID = CPI.CaseRefID
--		INNER JOIN tblCaseSubjectInfo CS ON CS.CaseSubjectID = A.CaseSubjectID
--		INNER JOIN tblStatusInfo S ON CPI.CaseStatus = S.StatusID
--		INNER JOIN tblCaseTypeInfo CT ON CPI.CaseTypeID = CT.CaseTypeID
--		LEFT JOIN tblUser U ON CPI.[ProcessedBy] = U.UserID
--		LEFT JOIN tblTeam TM ON U.TeamID = TM.TeamID
--		LEFT JOIN tblUser U2 ON CPI.[ClosedBy] = U2.UserID
--		LEFT JOIN tblTeam TM2 ON U2.TeamID = TM2.TeamID
--	WHERE CPI.CaseSourceID = 1 And CPI.IsReplyCase = 0 And CPI.CaseRefID = 7259
--	And CPI.CreatedDate Between @StartDate And @EndDate
--	Order By A.CreatedDate ASC


	--select count(*) FROM  tblCaseProfileIndex CPI
	--WHERE CPI.CaseSourceID = 2 And CPI.IsReplyCase = 0
	--And CPI.CreatedDate Between @StartDate And @EndDate


	

	--select top 10 * from tblCaseProfileIndex CPI
	--INNER JOIN tblContactProfile CP ON CPI.ContactProfileID = CP.ContactProfileID
	--LEFT OUTER JOIN tblTask T ON CPI.CaseRefID = T.CaseRefID And T.CaseSourceID = 2
	--LEFT OUTER JOIN tblStatusInfo S2 ON T.[Status] = S2.StatusID
	--Where CPI.CaseRefID = 23446 And CPI.CaseSourceID = 2


	--select top 10 * from tblCaseSubjectInfo

--***********************************************************
--**********CURRENT******************************************
--***********************************************************	

--SELECT Count(*)
--  FROM  tblCaseProfileIndex CPI
--		INNER JOIN tblEntity EN ON CPI.EntityID = EN.EntityID
--		INNER JOIN tblContactProfile CP ON CPI.ContactProfileID = CP.ContactProfileID
--		INNER JOIN [tblAskACRA] A  ON A.AskACRAID = CPI.CaseRefID And CPI.CaseSourceID = 1
--		INNER JOIN tblCaseSubjectInfo CS ON A.CaseSubjectID = CS.CaseSubjectID
--		INNER JOIN tblStatusInfo S ON CPI.CaseStatus = S.StatusID
--		INNER JOIN tblCaseTypeInfo CT ON CPI.CaseTypeID = CT.CaseTypeID
--		INNER JOIN tblUser U ON CPI.[ProcessedBy] = U.UserID
--		INNER JOIN tblTeam TM ON U.TeamID = TM.TeamID
--		INNER JOIN tblUser U2 ON CPI.[ClosedBy] = U2.UserID
--		INNER JOIN tblTeam TM2 ON U2.TeamID = TM2.TeamID
--		WHERE CPI.IsReplyCase = 0
--		And TM2.TeamTypeID = 1
--		And (Convert(varchar(6), CPI.ClosedOn, 112) = Convert(varchar(6), @EndDate, 112))

--SELECT Count(*)
--  FROM  tblCaseProfileIndex CPI
--		INNER JOIN tblEntity EN ON CPI.EntityID = EN.EntityID
--		INNER JOIN tblContactProfile CP ON CPI.ContactProfileID = CP.ContactProfileID
--		INNER JOIN [tblEmail] E  ON E.EmailID = CPI.CaseRefID And CPI.CaseSourceID = 2 
--		INNER JOIN tblStatusInfo S ON CPI.CaseStatus = S.StatusID
--		INNER JOIN tblCaseTypeInfo CT ON CPI.CaseTypeID = CT.CaseTypeID
--		INNER JOIN tblUser U ON CPI.[ProcessedBy] = U.UserID
--		INNER JOIN tblTeam TM ON U.TeamID = TM.TeamID
--		INNER JOIN tblUser U2 ON CPI.[ClosedBy] = U2.UserID
--		INNER JOIN tblTeam TM2 ON U2.TeamID = TM2.TeamID
--		WHERE CPI.IsReplyCase = 0
--		And TM2.TeamTypeID = 1
--		And (Convert(varchar(6), CPI.ClosedOn, 112) = Convert(varchar(6), @EndDate, 112))

SELECT E.[EmailID]
	  ,E.EmailCaseRefNo As [Case ID]
	  ,E.EmailSubject
	  ,CT.[Description] AS CaseType
	  ,CPI.CreatedDate As ReceivedOn
      ,CPI.[ProcessedBy]
	  ,CPI.[ProcessedOn]
	  ,CPI.[ClosedBy]
	  ,TM.TeamName As ProcessByDivision
	  ,U.DisplayName As ProcessByOfficer
	  ,TM2.TeamName As ClosedByDivision
	  ,U2.DisplayName As ClosedByOfficer
	  ,CPI.ClosedOn
	  ,CASE 
		WHEN (CPI.NoOfWorkingDays > 7) THEN '> 7D'
		WHEN (CPI.NoOfWorkingDays <= 3) THEN '>= 1D and =< 3D'
		WHEN (CPI.NoOfWorkingDays Between 4 And 7) THEN '> 3D And =< 7D'
		END ServiceStandard
	  ,CPI.NoOfWorkingDays
	  ,CPI.NoOfPHAndWeekendDays
	  ,CPI.CaseStatus
  FROM  tblCaseProfileIndex CPI
		INNER JOIN tblEntity EN ON CPI.EntityID = EN.EntityID
		INNER JOIN tblContactProfile CP ON CPI.ContactProfileID = CP.ContactProfileID
		INNER JOIN [tblEmail] E  ON E.EmailID = CPI.CaseRefID
		INNER JOIN tblStatusInfo S ON CPI.CaseStatus = S.StatusID
		INNER JOIN tblCaseTypeInfo CT ON CPI.CaseTypeID = CT.CaseTypeID
		INNER JOIN tblUser U ON CPI.[ProcessedBy] = U.UserID
		INNER JOIN tblTeam TM ON U.TeamID = TM.TeamID
		INNER JOIN tblUser U2 ON CPI.[ClosedBy] = U2.UserID
		INNER JOIN tblTeam TM2 ON U2.TeamID = TM2.TeamID
		WHERE CPI.CaseSourceID = 2 And CPI.IsReplyCase = 0
		And (Convert(varchar(6), CPI.ClosedOn, 112) = Convert(varchar(6), @EndDate, 112))
		Order By CPI.CreatedDate ASC

Select Convert(varchar(6), CAST('01-Jan-2014' as date), 112)

SELECT  A.AskACRAID
	  ,A.CaseRefNo As [Case ID]
	  ,CS.[Subject] CaseSubject
	  ,CT.[Description] AS CaseType
	  ,CPI.CreatedDate As ReceivedOn
      ,CPI.[ProcessedBy]
	  ,CPI.[ProcessedOn]
	  ,CPI.[ClosedBy]
	  ,TM.TeamName As ProcessByDivision
	  ,U.DisplayName As ProcessByOfficer
	  ,TM2.TeamName As ClosedByDivision
	  ,U2.DisplayName As ClosedByOfficer
	  ,CPI.ClosedOn
	  ,CASE 
		WHEN (CPI.NoOfWorkingDays > 7) THEN '> 7D'
		WHEN (CPI.NoOfWorkingDays <= 3) THEN '>= 1D and =< 3D'
		WHEN (CPI.NoOfWorkingDays Between 4 And 7) THEN '> 3D And =< 7D'
		END ServiceStandard
	  ,CPI.NoOfWorkingDays
	  ,CPI.NoOfPHAndWeekendDays
	  ,CPI.CaseStatus
  FROM  tblCaseProfileIndex CPI
		INNER JOIN tblEntity EN ON CPI.EntityID = EN.EntityID
		INNER JOIN tblContactProfile CP ON CPI.ContactProfileID = CP.ContactProfileID
		INNER JOIN [tblAskACRA] A  ON A.AskACRAID = CPI.CaseRefID And CPI.CaseSourceID = 1
		INNER JOIN tblCaseSubjectInfo CS ON A.CaseSubjectID = CS.CaseSubjectID
		INNER JOIN tblStatusInfo S ON CPI.CaseStatus = S.StatusID
		INNER JOIN tblCaseTypeInfo CT ON CPI.CaseTypeID = CT.CaseTypeID
		INNER JOIN tblUser U ON CPI.[ProcessedBy] = U.UserID
		INNER JOIN tblTeam TM ON U.TeamID = TM.TeamID
		INNER JOIN tblUser U2 ON CPI.[ClosedBy] = U2.UserID
		INNER JOIN tblTeam TM2 ON U2.TeamID = TM2.TeamID
		WHERE  CPI.IsReplyCase = 0
		And (Convert(varchar(6), CPI.ClosedOn, 112) = Convert(varchar(6), @EndDate, 112))
		Order By CPI.CreatedDate ASC

--Select count(*) FROM
--(Select distinct CPI.CaseProfileIndex from tblTask T 
--INNER JOIN tblCaseProfileIndex CPI ON T.CaseProfileIndex = CPI.CaseProfileIndex
--INNER JOIN tblTaskHistory TH ON T.TaskID = TH.TaskID
--INNER JOIN tblTeam TE ON TH.EscalatedTeamID = TE.TeamID
--Where T.CaseProfileIndex = CPI.CaseProfileIndex And TE.TeamTypeID = 2
--And  (dbo.funcGetWorkingDays(T.CaseReceivedDate, TH.CreatedDate)) <= 3
--And CPI.CreatedDate Between @StartDate And @EndDate
--And CPI.CaseSourceID = 1) AS TMP

--select count(*) As TotalIncomingAskACRACount from tblCaseProfileIndex CPI
--	INNER JOIN tblAskACRA A ON A.AskACRAID = CPI.CaseRefID And CPI.CaseSourceID = 1
--    Where IsReplyCase = 0
--    And CPI.CreatedDate Between @StartDate And @EndDate

--select Min(A.AskACRAID), Max(A.AskACRAID) from tblCaseProfileIndex CPI
--	INNER JOIN tblAskACRA A ON A.AskACRAID = CPI.CaseRefID And CPI.CaseSourceID = 1
--    Where IsReplyCase = 0
--    And CPI.CreatedDate Between @StartDate And @EndDate

--select count(E.EmailID) As TotalIncomingAskACRACount from tblCaseProfileIndex CPI
--	INNER JOIN tblEmail E ON E.EmailID = CPI.CaseRefID And CPI.CaseSourceID = 2
--	INNER JOIN tblEmailMessage EM ON E.EmailID = EM.EmailID 
--    Where IsReplyCase = 0
--    And CPI.CreatedDate Between @StartDate And @EndDate


--select Min(E.EmailID), Max(E.EmailID) As TotalIncomingAskACRACount from tblCaseProfileIndex CPI
--	INNER JOIN tblEmail E ON E.EmailID = CPI.CaseRefID And CPI.CaseSourceID = 2
--	INNER JOIN tblEmailMessage EM ON E.EmailID = EM.EmailID 
--    Where IsReplyCase = 0
--    And CPI.CreatedDate Between @StartDate And @EndDate

--select dbo.funcGetLastWorkingDateTime('01-Jan-2014')

--select * from tblPUblicholidays

--Jan 446	1901
--Feb 1902	3017
--Mar 3018	4778
--Apr 4779	6159
--May 6160	7737
--Jun 7738	9558
--July 9559	10255

--select count(*) As TotalIncomingAskACRACount from tblCaseProfileIndex CPI
--	INNER JOIN tblAskACRA A ON A.AskACRAID = CPI.CaseRefID And CPI.CaseSourceID = 1
--	INNER JOIN tblUser U ON CPI.ClosedBy = U.UserID
--	INNER JOIN tblTeam T ON T.TeamID = U.TeamID
--    Where IsReplyCase = 0 
--	And T.TeamTypeID = 1
--    And Convert(varchar(6), CPI.ClosedOn, 112) = Convert(varchar(6), @EndDate, 112)

--select count(*) As TotalIncomingAskACRACount from tblCaseProfileIndex CPI
--	INNER JOIN tblEmail E ON E.EmailID = CPI.CaseRefID And CPI.CaseSourceID = 2
--	INNER JOIN tblUser U ON CPI.ClosedBy = U.UserID
--	INNER JOIN tblTeam T ON T.TeamID = U.TeamID
--    Where IsReplyCase = 0 
--	And T.TeamTypeID = 1
--    And Convert(varchar(6), CPI.ClosedOn, 112) = Convert(varchar(6), @EndDate, 112)


